Форум dkLab и Denwer
Здесь общаются Web-разработчики.
Генеральный спонсор:
Хостинг «Джино»

Индексы и не правильная выборка (NetWork)
Author Message
NetWork
Участник форума



Joined: 11 May 2007
Posts: 46
Карма: -4
   поощрить/наказать


PostPosted: Mon Nov 19, 2007 10:33 am (написано за 16 секунд)
   Post subject: Индексы и не правильная выборка
Reply with quote

Мой знакомый недавно сказал мне, что у меня не правильно происходит выборка категорий и кол-ва аккаунтов в этой категории.

Ну структура таблиц простая:

Категории

CREATE TABLE `category` (
  `id` int(4) NOT NULL auto_increment,
  `sort` int(4) NOT NULL default '0',
  `name` varchar(55) NOT NULL,
  `title` varchar(200) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sort` (`sort`)
)

Аккаунты

CREATE TABLE `accounts` (
  `id` int(11) NOT NULL auto_increment,
  `name_site` varchar(200) NOT NULL default '',
  `url` varchar(200) NOT NULL default '',
  `host` varchar(200) NOT NULL default '',
  `email` varchar(200) NOT NULL default '',
  `name` varchar(200) NOT NULL default '',
  `status` tinyint(1) NOT NULL default '0',
  `user_login` varchar(20) NOT NULL default '',
  `user_pass` varchar(100) NOT NULL default '',
  `date` int(11) NOT NULL default '0',
  `category` int(4) NOT NULL default '0',
  PRIMARY KEY (`id`),
  KEY `category` (`category`)
)

Собственно мне нужно получить все категории и кол-во в каждой зарег-х аккаунтов. Я использую LEFT JOIN и делаю так.

SELECT category.id, category.name, category.title, COUNT(accounts.id) AS count
FROM category
LEFT JOIN accounts ON category.id=accounts.category
GROUP BY category.id
ORDER BY category.sort

Вроде я всё делаю правильно, а мне говорят, что у меня в индексах проблема и запрос получается сильно грузит машину. Помоги пожалуйста, я просто уже не знаю, что делать.
Back to top
View user's profile Send private message
Maus
Модератор



Joined: 29 Jun 2003
Posts: 8151
Карма: 271
   поощрить/наказать

Location: пос. Омсукчан Магаданской области

PostPosted: Mon Nov 19, 2007 11:49 pm (спустя 13 часов 16 минут; написано за 16 секунд)
   Post subject:
Reply with quote

NetWork
а без ORDER BY не обойтись?
Back to top
View user's profile Send private message
NetWork
Участник форума



Joined: 11 May 2007
Posts: 46
Карма: -4
   поощрить/наказать


PostPosted: Tue Nov 20, 2007 6:09 pm (спустя 18 часов 19 минут; написано за 2 минуты 28 секунд)
   Post subject:
Reply with quote

Ну обычно для сортировки категорий удобнее юзать отдельное поле. Но обойтись можно не спорю, но опять же не думаю, что это сильно влияет на нагрузку, я собственно не про это заморачивался. Мне больше сам LEFT JOIN интересует и то как раставлены индексы.
Back to top
View user's profile Send private message
Maus
Модератор



Joined: 29 Jun 2003
Posts: 8151
Карма: 271
   поощрить/наказать

Location: пос. Омсукчан Магаданской области

PostPosted: Wed Nov 21, 2007 10:09 pm (спустя 1 день 3 часа 59 минут; написано за 2 минуты 22 секунды)
   Post subject:
Reply with quote

NetWork
по-моему, с индексами у Вас всё в порядке - по тем полям, что в WHERE, индексы есть. Я имел в виду, что раз ORDER BY - значит, создаётся временная таблица и сортировка идет там.
могу, конечно, и заблуждаться... Что показывает EXPLAIN ?
Back to top
View user's profile Send private message
An6rey
Участник форума



Joined: 30 Aug 2007
Posts: 69
Карма: 3
   поощрить/наказать


PostPosted: Thu Nov 22, 2007 12:23 am (спустя 2 часа 14 минут; написано за 2 минуты 47 секунд)
   Post subject: Re: Индексы и не правильная выборка
Reply with quote

NetWork wrote:
SELECT category.id, category.name, category.title, COUNT(accounts.id) AS count
FROM category
LEFT JOIN accounts ON category.id=accounts.category
GROUP BY category.id
ORDER BY category.sort

Вроде я всё делаю правильно, а мне говорят, что у меня в индексах проблема и запрос получается сильно грузит машину. Помоги пожалуйста, я просто уже не знаю, что делать.
1) Все поля без функций агрегирования дожны присутствовать в условии GROUP
2) Попробуйте включить category.sort явно в результат выборки
3) Попробуйте использовать COUNT(*) - если это не нарушает логику выборки (я в нее не вникал)
Back to top
View user's profile Send private message
NetWork
Участник форума



Joined: 11 May 2007
Posts: 46
Карма: -4
   поощрить/наказать


PostPosted: Thu Nov 22, 2007 4:37 pm (спустя 16 часов 14 минут; написано за 4 минуты 28 секунд)
   Post subject:
Reply with quote

Maus wrote:
NetWork
по-моему, с индексами у Вас всё в порядке - по тем полям, что в WHERE, индексы есть. Я имел в виду, что раз ORDER BY - значит, создаётся временная таблица и сортировка идет там.
могу, конечно, и заблуждаться... Что показывает EXPLAIN ?
Code (SQL): скопировать код в буфер обмена
| id | select_type | TABLE    | type | possible_keys | KEY      | key_len | ref            |  rows | Extra 
| 1  | SIMPLE      | category | ALL  | NULL          | NULL     | NULL    | NULL           | 16    | USING TEMPORARY; USING filesort
| 1  | SIMPLE      | accounts | ref  | category      | category | 4       | bb.category.id | 3     |
Back to top
View user's profile Send private message
NetWork
Участник форума



Joined: 11 May 2007
Posts: 46
Карма: -4
   поощрить/наказать


PostPosted: Thu Nov 22, 2007 4:43 pm (спустя 6 минут; написано за 5 минут 25 секунд)
   Post subject: Re: Индексы и не правильная выборка
Reply with quote

An6rey wrote:
NetWork wrote:
SELECT category.id, category.name, category.title, COUNT(accounts.id) AS count
FROM category
LEFT JOIN accounts ON category.id=accounts.category
GROUP BY category.id
ORDER BY category.sort

Вроде я всё делаю правильно, а мне говорят, что у меня в индексах проблема и запрос получается сильно грузит машину. Помоги пожалуйста, я просто уже не знаю, что делать.
1) Все поля без функций агрегирования дожны присутствовать в условии GROUP
2) Попробуйте включить category.sort явно в результат выборки
3) Попробуйте использовать COUNT(*) - если это не нарушает логику выборки (я в нее не вникал)
Ну вот category.sort - зачем мне её в результат если я её не буду использовать. А COUNT(*) - так лучше же точно указать, а не просто( *)
Back to top
View user's profile Send private message
dimagolov
Участник форума



Joined: 04 Feb 2007
Posts: 1664
Карма: 96
   поощрить/наказать

Location: Christ Church, Barbados

PostPosted: Thu Nov 22, 2007 5:06 pm (спустя 22 минуты; написано за 3 минуты 34 секунды)
   Post subject:
Reply with quote

Code (SQL): скопировать код в буфер обмена
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `fCountAccounts`(categoryId int) RETURNS int
BEGIN
DECLARE CountAccounts int;
SELECT COUNT(*) INTO CountAccounts  FROM accounts WHERE category = categoryId;
RETURN CountAccounts;
END;;
DELIMITER ;

SELECT id, name, title, fCountAccounts(id) AS count
FROM category
ORDER BY category.sort;
Back to top
View user's profile Send private message
An6rey
Участник форума



Joined: 30 Aug 2007
Posts: 69
Карма: 3
   поощрить/наказать


PostPosted: Thu Nov 22, 2007 6:59 pm (спустя 1 час 53 минуты; написано за 8 минут 11 секунд)
   Post subject: Re: Индексы и не правильная выборка
Reply with quote

NetWork wrote:
Ну вот category.sort - зачем мне её в результат если я её не буду использовать. А COUNT(*) - так лучше же точно указать, а не просто( *)
По category.sort - сортировка будет происходить в другом месте - может ускорить код. Вы же используете в ORDER BY category.sort.
По COUNT(*) - просто посчитает количество строк - будет тоже работать быстрее.
Back to top
View user's profile Send private message
KES
Участник форума



Joined: 19 Oct 2006
Posts: 214
Карма: 0
   поощрить/наказать

Location: сВознесеновка, Мелитопольский р-н, Запорожская обл. Украина

PostPosted: Tue Nov 27, 2007 3:44 pm (спустя 4 дня 20 часов 44 минуты; написано за 4 минуты 48 секунд)
   Post subject:
Reply with quote

Переводим на SQL язык:
выбрать все категории
SELECT c.id, c.name
FROM category c
ORDER BY c.sort

Посчитать кол-во пользователей в данной категории:
select count(a.id) from accounts a where a.category = CATEGORY_ID


Теперь одним запросом:
SELECT c.id, c.name, c.title, (select count(a.id) from accounts a where a.category = c.id) AS count
FROM category c
ORDER BY c.sort

IMXO:
Если поле - это ссылка на другую таблицу, то лучше называть его как "имя таблицы"+"_ID"
`category_ID` int(4) NOT NULL default '0',
`ID` int(4) NOT NULL auto_increment, и лучше ID писать большими буквами, это заставляет на него обращать внимание как на особенное поле!
Back to top
View user's profile Send private message
dimagolov
Участник форума



Joined: 04 Feb 2007
Posts: 1664
Карма: 96
   поощрить/наказать

Location: Christ Church, Barbados

PostPosted: Tue Nov 27, 2007 4:17 pm (спустя 32 минуты; написано за 1 минуту 33 секунды)
   Post subject:
Reply with quote

KES, а теперь найдите отличия от моего кода (кроме того, что запрос с подсчетом пользователей у меня оформлен хранимой ф-ей) :)
Back to top
View user's profile Send private message
KES
Участник форума



Joined: 19 Oct 2006
Posts: 214
Карма: 0
   поощрить/наказать

Location: сВознесеновка, Мелитопольский р-н, Запорожская обл. Украина

PostPosted: Tue Nov 27, 2007 7:22 pm (спустя 3 часа 5 минут; написано за 6 минут 58 секунд)
   Post subject:
Reply with quote

Отличий нет, разве что отличия в стиле =)
Вот те НО, на которые хотелось бы обратить внимания

Минусы:
1. Гораздо длинней и
2. Мозг загружается конструкциями создания функции
3. Длиннее код - большая вероятность ошибки
4а. Зачем лишняя функция в базе, которая создана специально только для этого запроса?

Плюсы:
4а. Выделили общий запрос в функцию, которая будет использована в N местах
5. Ваш вариант наглядней
Code (SQL): скопировать код в буфер обмена
SELECT id, name, title, fCountAccounts(id) AS count
FROM category
ORDER BY category.sort;
Хотя при верном оформлении в моём тоже видна суть:
Code (SQL): скопировать код в буфер обмена
SELECT
 c.id,
 c.name,
 c.title,
 (SELECT count(a.id) FROM accounts a WHERE a.category = c.id) AS AccountsCount
FROM category c
ORDER BY c.sort
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic All times are GMT + 3 Hours
Page 1 of 1    Email to a Friend.
You cannot post new topics in this forum. You cannot reply to topics in this forum. You cannot edit your posts in this forum. You cannot delete your posts in this forum. You cannot vote in polls in this forum. You cannot attach files in this forum. You can download files in this forum.
XML